﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace Lucky_bgy
{
    class DBHelper
    {
        private OleDbConnection connection = null;

    //----创建连接串并连接数据库----
    public DBHelper(string path, string password)
    {
      string conn_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Jet OLEDB:Database Password= " + password;
      connection = new OleDbConnection(conn_str); 
      connection.Open();
    }

    public DBHelper()
    {
        string conn_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='"+System.Environment.CurrentDirectory+"\\db\\bgy_lucky.mdb';Jet OLEDB:Database Password=yangtao";
        connection = new OleDbConnection(conn_str);
        connection.Open();
    }

    //----关闭数据库连接----
    public void CloseConnection()
    {
      connection.Close();
      connection = null;
    }

    /// <summary> 
    /// 执行一个查询语句，返回一个包含查询结果的DataTable 
    /// </summary> 
    /// <param name="sql">要执行的查询语句</param> 
    /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param> 
    /// <returns></returns> 
    public DataTable ExecuteDataTable(string sql, OleDbParameter[] parameters)
    {
      try
      {
        using (OleDbCommand Command = new OleDbCommand(sql, connection))
        {
          if (parameters != null)
          {
            Command.Parameters.AddRange(parameters);
          }
          OleDbDataAdapter adapter = new OleDbDataAdapter(Command);
          DataTable dataTable = new DataTable();
          adapter.Fill(dataTable);
          return dataTable;
        }
      }
      catch (OleDbException ex)
      {
        System.Exception exc = ex;
        throw (exc);
      }
     
    }

    /// <summary> 
    /// 对Access数据库执行增删改操作，返回受影响的行数。 
    /// </summary> 
    /// <param name="sql">要执行的增删改的SQL语句</param> 
    /// <param name="parameters">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param> 
    /// <returns></returns> 
    public int ExecuteNonQuery(string sql, OleDbParameter[] parameters)
    {
      int affectRows = 0;

      try
      {
        using (OleDbTransaction Transaction = connection.BeginTransaction())
        {
          using (OleDbCommand Command = new OleDbCommand(sql, connection, Transaction))
          {
            if (parameters != null)
            {
              Command.Parameters.AddRange(parameters);
            }
            affectRows = Command.ExecuteNonQuery();
          }
          Transaction.Commit();
        }
      }
      catch (OleDbException ex)
      {
        affectRows = -1;
        //log.Error("ExecuteNonQuery occurs exception：" + ex.Message);
      }
      return affectRows;
    }

    /// <summary>
    /// datatable直接导入数据库
    /// </summary>
    /// <param name="temp">datatable</param>
    /// <param name="selectSql">与dt字段匹配的查询语句（不带条件）</param>
    public void UpdateAccess(DataTable temp,string selectSql)
    {
        try
        {
            OleDbDataAdapter Bada = new OleDbDataAdapter(selectSql + " where 1 =2", connection);//建立一个DataAdapter对象
            OleDbCommandBuilder cb = new OleDbCommandBuilder(Bada);//这里的CommandBuilder对象一定不要忘了,一般就是写在DataAdapter定义的后面
            cb.QuotePrefix = "[";
            cb.QuoteSuffix = "]";
            DataSet ds = new DataSet();//建立DataSet对象
            Bada.Fill(ds, "demo");//填充DataSet
            foreach (DataRow tempRow in temp.Rows)
            {
                DataRow dr = ds.Tables["demo"].NewRow();
                dr.ItemArray = tempRow.ItemArray;//行复制
                ds.Tables["demo"].Rows.Add(dr);
            }
            Bada.Update(ds, "demo");//用DataAdapter的Update()方法进行数据库的更新
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }
    }
  }
}